package br.com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import br.com.bean.SerieBean;
import br.com.bean.TurmaBean;

public class TurmaDao {

	public void inserir (Connection conn, TurmaBean value) throws Exception{
		
		String sql = "INSERT INTO cad_turma (id_ensino, id_serie, id_turno, turma, dias_letivos, ano_letivo) VALUES (?, ?, ?, ?, ?, ?)";
		
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, value.getEnsino().getId());
		ps.setInt(2, value.getSerie().getId());
		ps.setInt(3, value.getTurno().getId());
		ps.setString(4, value.getTurma());
		ps.setInt(5, value.getDiaslet());
		ps.setInt(6,value.getAnolet());
		ps.execute();
		ps.close();
		
	}
	
	public void alterar (Connection conn, TurmaBean value) throws Exception{
		
		String sql = "UPDATE cad_turma SET id_ensino = ?, id_serie = ?, id_turno = ?, turma = ?, dias_letivos = ?, ano_letivo = ? WHERE  id = ?";
		
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, value.getEnsino().getId());
		ps.setInt(2, value.getSerie().getId());
		ps.setInt(3, value.getTurno().getId());
		ps.setString(4, value.getTurma());
		ps.setInt(5, value.getDiaslet());
		ps.setInt(6,value.getAnolet());
		ps.close();

	}
	
	public void excluir (Connection conn, TurmaBean value) throws Exception {

		String sql = "DELETE FROM cad_turma WHERE where id = ?";
		
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, value.getId());
		ps.executeUpdate();
	}
	
	public ArrayList<TurmaBean> getLista(Connection conn, TurmaBean value) throws Exception{

		String sql = "SELECT id_ensino, id_serie, id_turno, turma, dias_letivos, ano_letivo FROM cad_turma where id = ?";
		
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, value.getId());
		ResultSet rs = ps.executeQuery();
		ArrayList<TurmaBean> cadturma = new ArrayList<TurmaBean>();
		while(rs.next()){
			TurmaBean a = new TurmaBean();
			a.getEnsino().setId(rs.getInt("id_ensino"));
			a.getSerie().setId(rs.getInt("id_serie"));
			a.getTurno().setId(rs.getInt("id_turno"));
			a.setTurma(rs.getString("turma"));
			a.setDiaslet(rs.getInt("dias_letivos"));
			a.setAnolet(rs.getInt("ano_letivo"));
			cadturma.add(a);
		}
		return cadturma;
	}
	
	public ArrayList<TurmaBean> getLista(Connection conn) throws Exception{

		String sql = "SELECT id_ensino, id_serie, id_turno, turma, dias_letivos, ano_letivo FROM";
		
		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		ArrayList<TurmaBean> cadturma = new ArrayList<TurmaBean>();
		while(rs.next()){
			TurmaBean a = new TurmaBean();
			a.getEnsino().setId(rs.getInt("id_ensino"));
			a.getSerie().setId(rs.getInt("id_serie"));
			a.getTurno().setId(rs.getInt("id_turno"));
			a.setTurma(rs.getString("turma"));
			a.setDiaslet(rs.getInt("dias_letivos"));
			a.setAnolet(rs.getInt("ano_letivo"));
			cadturma.add(a);
		}
		return cadturma;
	}
	// combo dinamico de turma e serie
	public List<TurmaBean> getListaTurma (Connection conn, SerieBean value) throws Exception{

		String sql = "SELECT id, turma FROM cad_turma ct where id_serie = ? AND ct.id = ct.id order by ct.turma;";
		
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, value.getId());
		ResultSet rs = ps.executeQuery();
		List<TurmaBean> turma = new ArrayList<TurmaBean>();
		TurmaBean a = null;
		while(rs.next()){
			a = new TurmaBean();
			a.setId(rs.getInt("id"));
			a.setTurma(rs.getString("turma"));
			turma.add(a);
		}
		return turma;
	}
	
}
